use EnterpriseServices
go

update Core.GroupUsers set IsGroupOwner = 0
go
delete from Core.Groups
go
delete from Core.Applications
go
delete from Core.Users
GO


-- insert Enumeration values
DECLARE @RC int
DECLARE @ParameterName nvarchar(256) = N'GroupState'
DECLARE @EnumerationValue nvarchar(256) = N'Normal'
DECLARE @EnumerationIndex int = 0
DECLARE @ValueId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_EnumValue_insertGetValue] 
   @ParameterName
  ,@EnumerationValue
  ,@EnumerationIndex
  ,@ValueId OUTPUT
PRINT @ValueId
PRINT @RC
GO
DECLARE @RC int
DECLARE @ParameterName nvarchar(256) = N'GroupState'
DECLARE @EnumerationValue nvarchar(256) = N'InExpirationTimespan'
DECLARE @EnumerationIndex int = 1
DECLARE @ValueId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_EnumValue_insertGetValue] 
   @ParameterName
  ,@EnumerationValue
  ,@EnumerationIndex
  ,@ValueId OUTPUT
PRINT @ValueId
PRINT @RC
GO
DECLARE @RC int
DECLARE @ParameterName nvarchar(256) = N'GroupState'
DECLARE @EnumerationValue nvarchar(256) = N'Expired'
DECLARE @EnumerationIndex int = 2
DECLARE @ValueId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_EnumValue_insertGetValue] 
   @ParameterName
  ,@EnumerationValue
  ,@EnumerationIndex
  ,@ValueId OUTPUT
PRINT @ValueId
PRINT @RC
GO






-- insert Application

DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @Description nvarchar(256) = N'Field Measurement System'
DECLARE @ApplicationId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Applications_CreateApplication] 
   @ApplicationName
  ,@Description
  ,@ApplicationId OUTPUT
PRINT @ApplicationId
GO




-- insert Roles

DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'SystemAdministrator'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_CreateRole] 
   @ApplicationName
  ,@RoleName
PRINT @RC
GO

DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'SecurityAdministrator'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_CreateRole] 
   @ApplicationName
  ,@RoleName
PRINT @RC
GO

DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'Administrator'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_CreateRole] 
   @ApplicationName
  ,@RoleName
PRINT @RC
GO

DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'StandardUser'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_CreateRole] 
   @ApplicationName
  ,@RoleName
PRINT @RC
GO

DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'GuestUser'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_CreateRole] 
   @ApplicationName
  ,@RoleName
PRINT @RC
GO




-- insert users

DECLARE @RC int
DECLARE @UserName nvarchar(256) = N'WGINT\nichsr27'
DECLARE @IsUserAnonymous bit = 0
DECLARE @IsADDeployed bit = 1
DECLARE @LastActivityDate datetime = GETDATE()
DECLARE @UserId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Users_CreateUser] 
   @UserName
  ,@IsUserAnonymous
  ,@IsADDeployed
  ,@LastActivityDate
  ,@UserId OUTPUT
PRINT @UserId
PRINT @RC
GO

DECLARE @RC int
DECLARE @UserName nvarchar(256) = N'Edwin'
DECLARE @IsUserAnonymous bit = 0
DECLARE @IsADDeployed bit = 1
DECLARE @LastActivityDate datetime = GETDATE()
DECLARE @UserId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Users_CreateUser] 
   @UserName
  ,@IsUserAnonymous
  ,@IsADDeployed
  ,@LastActivityDate
  ,@UserId OUTPUT
PRINT @UserId
PRINT @RC
GO

DECLARE @RC int
DECLARE @UserName nvarchar(256) = N'WGINT\lagub447'
DECLARE @IsUserAnonymous bit = 0
DECLARE @IsADDeployed bit = 1
DECLARE @LastActivityDate datetime = GETDATE()
DECLARE @UserId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Users_CreateUser] 
   @UserName
  ,@IsUserAnonymous
  ,@IsADDeployed
  ,@LastActivityDate
  ,@UserId OUTPUT
PRINT @UserId
PRINT @RC
GO

DECLARE @RC int
DECLARE @UserName nvarchar(256) = N'WGINT\burrd001'
DECLARE @IsUserAnonymous bit = 0
DECLARE @IsADDeployed bit = 1
DECLARE @LastActivityDate datetime = GETDATE()
DECLARE @UserId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Users_CreateUser] 
   @UserName
  ,@IsUserAnonymous
  ,@IsADDeployed
  ,@LastActivityDate
  ,@UserId OUTPUT
PRINT @UserId
PRINT @RC
GO

DECLARE @RC int
DECLARE @UserName nvarchar(256) = N'WGINT\klehs208'
DECLARE @IsUserAnonymous bit = 0
DECLARE @IsADDeployed bit = 1
DECLARE @LastActivityDate datetime = GETDATE()
DECLARE @UserId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Users_CreateUser] 
   @UserName
  ,@IsUserAnonymous
  ,@IsADDeployed
  ,@LastActivityDate
  ,@UserId OUTPUT
PRINT @UserId
PRINT @RC
GO

DECLARE @RC int
DECLARE @UserName nvarchar(256) = N'WGINT\wayne.worthon'
DECLARE @IsUserAnonymous bit = 0
DECLARE @IsADDeployed bit = 1
DECLARE @LastActivityDate datetime = GETDATE()
DECLARE @UserId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Users_CreateUser] 
   @UserName
  ,@IsUserAnonymous
  ,@IsADDeployed
  ,@LastActivityDate
  ,@UserId OUTPUT
PRINT @UserId
PRINT @RC
GO





-- insert Groups: Departments

DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'URS Headquarters'
DECLARE @ParentGroupName nvarchar(256)
DECLARE @GroupType int = 3
DECLARE @GroupExpiration datetime2(7)
DECLARE @IsADDeployed bit = 1
DECLARE @CreatorUserName nvarchar(256) = N'WGINT\klehs208'
DECLARE @GroupId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_CreateGroup] 
   @GroupName
  ,@ParentGroupName
  ,@GroupType
  ,@GroupExpiration
  ,@IsADDeployed
  ,@CreatorUserName
  ,@GroupId OUTPUT
PRINT @GroupId
PRINT @RC
GO
DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'URS Headquarters'
DECLARE @EnumerationName nvarchar(256) = N'GroupState'
DECLARE @EnumerationIndex int = 0
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_SetGroupState] 
   @GroupName
  ,@EnumerationName
  ,@EnumerationIndex
PRINT @RC
GO

DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'Washington Division'
DECLARE @ParentGroupName nvarchar(256) = N'URS Headquarters'
DECLARE @GroupType int = 3
DECLARE @GroupExpiration datetime2(7)
DECLARE @IsADDeployed bit = 1
DECLARE @CreatorUserName nvarchar(256) = N'WGINT\klehs208'
DECLARE @GroupId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_CreateGroup] 
   @GroupName
  ,@ParentGroupName
  ,@GroupType
  ,@GroupExpiration
  ,@IsADDeployed
  ,@CreatorUserName
  ,@GroupId OUTPUT
PRINT @GroupId
PRINT @RC
GO
DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'Washington Division'
DECLARE @EnumerationName nvarchar(256) = N'GroupState'
DECLARE @EnumerationIndex int = 0
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_SetGroupState] 
   @GroupName
  ,@EnumerationName
  ,@EnumerationIndex
PRINT @RC
GO

DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'IT Department'
DECLARE @ParentGroupName nvarchar(256) = N'Washington Division'
DECLARE @GroupType int = 3
DECLARE @GroupExpiration datetime2(7)
DECLARE @IsADDeployed bit = 1
DECLARE @CreatorUserName nvarchar(256) = N'WGINT\klehs208'
DECLARE @GroupId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_CreateGroup] 
   @GroupName
  ,@ParentGroupName
  ,@GroupType
  ,@GroupExpiration
  ,@IsADDeployed
  ,@CreatorUserName
  ,@GroupId OUTPUT
PRINT @GroupId
PRINT @RC
GO
DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'IT Department'
DECLARE @EnumerationName nvarchar(256) = N'GroupState'
DECLARE @EnumerationIndex int = 0
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_SetGroupState] 
   @GroupName
  ,@EnumerationName
  ,@EnumerationIndex
PRINT @RC
GO




-- insert Groups: Public

DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'Applications'
DECLARE @ParentGroupName nvarchar(256)
DECLARE @GroupType int = 0
DECLARE @GroupExpiration datetime2(7)
DECLARE @IsADDeployed bit = 1
DECLARE @CreatorUserName nvarchar(256) = N'WGINT\nichsr27'
DECLARE @GroupId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_CreateGroup] 
   @GroupName
  ,@ParentGroupName
  ,@GroupType
  ,@GroupExpiration
  ,@IsADDeployed
  ,@CreatorUserName
  ,@GroupId OUTPUT
PRINT @GroupId
PRINT @RC
GO
DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'Applications'
DECLARE @EnumerationName nvarchar(256) = N'GroupState'
DECLARE @EnumerationIndex int = 0
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_SetGroupState] 
   @GroupName
  ,@EnumerationName
  ,@EnumerationIndex
PRINT @RC
GO

DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'FMS'
DECLARE @ParentGroupName nvarchar(256) = N'Applications'
DECLARE @GroupType int = 0
DECLARE @GroupExpiration datetime2(7)
DECLARE @IsADDeployed bit = 1
DECLARE @CreatorUserName nvarchar(256) = N'WGINT\nichsr27'
DECLARE @GroupId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_CreateGroup] 
   @GroupName
  ,@ParentGroupName
  ,@GroupType
  ,@GroupExpiration
  ,@IsADDeployed
  ,@CreatorUserName
  ,@GroupId OUTPUT
PRINT @GroupId
PRINT @RC
GO
DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'FMS'
DECLARE @EnumerationName nvarchar(256) = N'GroupState'
DECLARE @EnumerationIndex int = 0
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_SetGroupState] 
   @GroupName
  ,@EnumerationName
  ,@EnumerationIndex
PRINT @RC
GO

DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'FMSAdministrators'
DECLARE @ParentGroupName nvarchar(256) = N'FMS'
DECLARE @GroupType int = 0
DECLARE @GroupExpiration datetime2(7)
DECLARE @IsADDeployed bit = 1
DECLARE @CreatorUserName nvarchar(256) = N'WGINT\nichsr27'
DECLARE @GroupId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_CreateGroup] 
   @GroupName
  ,@ParentGroupName
  ,@GroupType
  ,@GroupExpiration
  ,@IsADDeployed
  ,@CreatorUserName
  ,@GroupId OUTPUT
PRINT @GroupId
PRINT @RC
GO
DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'FMSAdministrators'
DECLARE @EnumerationName nvarchar(256) = N'GroupState'
DECLARE @EnumerationIndex int = 0
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_SetGroupState] 
   @GroupName
  ,@EnumerationName
  ,@EnumerationIndex
PRINT @RC
GO

DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'FMSUsers'
DECLARE @ParentGroupName nvarchar(256) = N'FMS'
DECLARE @GroupType int = 0
DECLARE @GroupExpiration datetime2(7)
DECLARE @IsADDeployed bit = 1
DECLARE @CreatorUserName nvarchar(256) = N'WGINT\nichsr27'
DECLARE @GroupId uniqueidentifier
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_CreateGroup] 
   @GroupName
  ,@ParentGroupName
  ,@GroupType
  ,@GroupExpiration
  ,@IsADDeployed
  ,@CreatorUserName
  ,@GroupId OUTPUT
PRINT @GroupId
PRINT @RC
GO
DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'FMSUsers'
DECLARE @EnumerationName nvarchar(256) = N'GroupState'
DECLARE @EnumerationIndex int = 0
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_SetGroupState] 
   @GroupName
  ,@EnumerationName
  ,@EnumerationIndex
PRINT @RC
GO

DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'FMSUsers'
DECLARE @NewParentGroupName nvarchar(256) = N'FMSAdministrators'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Groups_DoMoveGroup] 
   @GroupName
  ,@NewParentGroupName
PRINT @RC
GO







-- assign a group to a role

DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'StandardUser'
DECLARE @GroupName nvarchar(256) = N'FMSUsers'
EXECUTE @RC = [EnterpriseServices].[Core].[p_RoleGroups_insertRoleGroup] 
   @ApplicationName
  ,@RoleName
  ,@GroupName
PRINT @RC
GO

-- should return 1
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'StandardUser'
DECLARE @GroupName nvarchar(256) = N'FMSUsers'
EXECUTE @RC = [EnterpriseServices].[Core].[p_RoleGroups_IsGroupInRole] 
   @ApplicationName
  ,@RoleName
  ,@GroupName
PRINT @RC
GO
-- should return 1
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'StandardUser'
DECLARE @GroupName nvarchar(256) = N'FMSAdministrators'
EXECUTE @RC = [EnterpriseServices].[Core].[p_RoleGroups_IsGroupInRole] 
   @ApplicationName
  ,@RoleName
  ,@GroupName
PRINT @RC
GO
-- should return 0
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'Administrator'
DECLARE @GroupName nvarchar(256) = N'FMSAdministrators'
EXECUTE @RC = [EnterpriseServices].[Core].[p_RoleGroups_IsGroupInRole] 
   @ApplicationName
  ,@RoleName
  ,@GroupName
PRINT @RC
GO






-- assign a group to a role

DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'SecurityAdministrator'
DECLARE @GroupName nvarchar(256) = N'FMSAdministrators'
EXECUTE @RC = [EnterpriseServices].[Core].[p_RoleGroups_insertRoleGroup] 
   @ApplicationName
  ,@RoleName
  ,@GroupName
PRINT @RC
GO

-- should return 1
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'SecurityAdministrator'
DECLARE @GroupName nvarchar(256) = N'FMSAdministrators'
EXECUTE @RC = [EnterpriseServices].[Core].[p_RoleGroups_IsGroupInRole] 
   @ApplicationName
  ,@RoleName
  ,@GroupName
PRINT @RC
GO
-- should return 1
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'SecurityAdministrator'
DECLARE @GroupName nvarchar(256) = N'Applications'
EXECUTE @RC = [EnterpriseServices].[Core].[p_RoleGroups_IsGroupInRole] 
   @ApplicationName
  ,@RoleName
  ,@GroupName
PRINT @RC
GO
-- should return 0
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'SecurityAdministrator'
DECLARE @GroupName nvarchar(256) = N'FMSUsers'
EXECUTE @RC = [EnterpriseServices].[Core].[p_RoleGroups_IsGroupInRole] 
   @ApplicationName
  ,@RoleName
  ,@GroupName
PRINT @RC
GO






-- add WGINT\klehs208 to the FMSUsers

DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'FMSUsers'
DECLARE @UserName nvarchar(256) = N'WGINT\klehs208'
DECLARE @IsGroupOwner bit = 0
DECLARE @IsGroupContact bit = 0
DECLARE @IsGroupMember bit = 1
DECLARE @InsertingUserName nvarchar(256) = N'WGINT\nichsr27'
EXECUTE @RC = [EnterpriseServices].[Core].[p_GroupUsers_insertGroupUser] 
   @GroupName
  ,@UserName
  ,@IsGroupOwner
  ,@IsGroupContact
  ,@IsGroupMember
  ,@InsertingUserName
PRINT @RC
GO
-- return the groups for a user that allow him to get access to roles (only Member and Owner)
CREATE TABLE #GroupUsersResults (
	GroupId uniqueidentifier,
	UserId uniqueidentifier,
	IsGroupOwner bit,
	IsGroupContact bit,
	IsGroupMember bit
)
DECLARE @RC int
DECLARE @UserName nvarchar(256) = N'WGINT\klehs208'
EXECUTE @RC = [EnterpriseServices].[Core].[p_GroupUsers_GetGroupUsersByUser] @UserName
select g.*, gur.* from #GroupUsersResults gur INNER JOIN Core.Groups g ON gur.GroupId = g.GroupId WHERE gur.IsGroupMember = 1 OR gur.IsGroupOwner = 1
GO
-- the resultset should contain only the StandardUser role
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\klehs208'
CREATE TABLE #RolesResults (
	RoleId uniqueidentifier
)
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_GetRolesByUser] 
   @ApplicationName
  ,@UserName
PRINT @RC
Select r.* FROM #RolesResults rr INNER JOIN Core.Roles r ON rr.RoleId = r.RoleId
GO

-- should return 0
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @RoleName nvarchar(256) = N'Administrator'
DECLARE @GroupName nvarchar(256) = N'Applications'
EXECUTE @RC = [EnterpriseServices].[Core].[p_RoleGroups_IsGroupInRole] 
   @ApplicationName
  ,@RoleName
  ,@GroupName
PRINT @RC
GO
-- should return 1
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\klehs208'
DECLARE @RoleName nvarchar(256) = N'StandardUser'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_IsUserInRole] 
   @ApplicationName
  ,@UserName
  ,@RoleName
PRINT @RC
GO
-- let us see the data
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\nichsr27'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_GetRolesByUser] 
   @ApplicationName
  ,@UserName
select distinct r.* from #RolesResults rr INNER JOIN Core.Roles r ON rr.RoleId = r.RoleId
GO
-- should return 0
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\klehs208'
DECLARE @RoleName nvarchar(256) = N'SecurityAdministrator'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_IsUserInRole] 
   @ApplicationName
  ,@UserName
  ,@RoleName
PRINT @RC
GO
-- should return 1
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\klehs208'
DECLARE @RoleName nvarchar(256) = N'StandardUser'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_IsUserInRole] 
   @ApplicationName
  ,@UserName
  ,@RoleName
PRINT @RC
GO
-- should return 1
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\nichsr27'
DECLARE @RoleName nvarchar(256) = N'SecurityAdministrator'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_IsUserInRole] 
   @ApplicationName
  ,@UserName
  ,@RoleName
PRINT @RC
GO
-- should return 1
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\nichsr27'
DECLARE @RoleName nvarchar(256) = N'StandardUser'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_IsUserInRole] 
   @ApplicationName
  ,@UserName
  ,@RoleName
PRINT @RC
GO








-- add an associated table
--	IT Department to FMS Users. In Result, WGINT\lagub447 should be in the FMS StandardUser role
-- however, we will do some tests, first

-- should return 2 .. group does not exist
DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'FMSUsers'
DECLARE @AssociatedGroupName nvarchar(256) = N'IT Depart'
DECLARE @InsertingUserName nvarchar(256) = N'WGINT\klehs208'
EXECUTE @RC = [EnterpriseServices].[Core].[p_GroupAssociations_insertAssociatedGroups] 
   @GroupName
  ,@AssociatedGroupName
  ,@InsertingUserName
PRINT @RC
GO
-- should return 4 .. WGINT\klehs208 does not have permission to associate the group
DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'FMSUsers'
DECLARE @AssociatedGroupName nvarchar(256) = N'IT Department'
DECLARE @InsertingUserName nvarchar(256) = N'WGINT\klehs208'
EXECUTE @RC = [EnterpriseServices].[Core].[p_GroupAssociations_insertAssociatedGroups] 
   @GroupName
  ,@AssociatedGroupName
  ,@InsertingUserName
PRINT @RC
GO
-- should return 0
DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'FMSUsers'
DECLARE @AssociatedGroupName nvarchar(256) = N'IT Department'
DECLARE @InsertingUserName nvarchar(256) = N'WGINT\nichsr27'
EXECUTE @RC = [EnterpriseServices].[Core].[p_GroupAssociations_insertAssociatedGroups] 
   @GroupName
  ,@AssociatedGroupName
  ,@InsertingUserName
PRINT @RC
GO





-- add WGINT\lagub447 to the IT Department group as a member
DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'IT Department'
DECLARE @UserName nvarchar(256) = N'WGINT\lagub447'
DECLARE @IsGroupOwner bit = 0
DECLARE @IsGroupContact bit = 0
DECLARE @IsGroupMember bit = 1
DECLARE @InsertingUserName nvarchar(256) = N'WGINT\nichsr27'
EXECUTE @RC = [EnterpriseServices].[Core].[p_GroupUsers_insertGroupUser] 
   @GroupName
  ,@UserName
  ,@IsGroupOwner
  ,@IsGroupContact
  ,@IsGroupMember
  ,@InsertingUserName
PRINT @RC
GO
-- let us see the data
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\lagub447'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_GetRolesByUser] 
   @ApplicationName
  ,@UserName
select r.* from #RolesResults rr INNER JOIN Core.Roles r ON rr.RoleId = r.RoleId
GO

-- check, if WGINT\lagub447 is now a member of the StandardUser role in FMS
-- should return 1
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\lagub447'
DECLARE @RoleName nvarchar(256) = N'StandardUser'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_IsUserInRole] 
   @ApplicationName
  ,@UserName
  ,@RoleName
PRINT @RC
GO

-- should return 0
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\lagub447'
DECLARE @RoleName nvarchar(256) = N'SecurityAdministrator'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_IsUserInRole] 
   @ApplicationName
  ,@UserName
  ,@RoleName
PRINT @RC
GO
-- should return 1
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\klehs208'
DECLARE @RoleName nvarchar(256) = N'StandardUser'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_IsUserInRole] 
   @ApplicationName
  ,@UserName
  ,@RoleName
PRINT @RC
GO






-- now, we will see if GetRolesByUser works correctly
-- we add the IT DEPARTMENT group to the FMS group later by building an association between them
-- in result, WGINT\klehs208 should be in the StandardUser role

-- should return 0
DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'FMS'
DECLARE @AssociatedGroupName nvarchar(256) = N'IT Department'
DECLARE @InsertingUserName nvarchar(256) = N'WGINT\nichsr27'
EXECUTE @RC = [EnterpriseServices].[Core].[p_GroupAssociations_insertAssociatedGroups] 
   @GroupName
  ,@AssociatedGroupName
  ,@InsertingUserName
PRINT @RC
GO
-- let us see the data. WGINT\klehs208 should be in StandardUser now
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\klehs208'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_GetRolesByUser] 
   @ApplicationName
  ,@UserName
select distinct r.* from #RolesResults rr INNER JOIN Core.Roles r ON rr.RoleId = r.RoleId
GO
-- should return 0
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\klehs208'
DECLARE @RoleName nvarchar(256) = N'SecurityAdministrator'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_IsUserInRole] 
   @ApplicationName
  ,@UserName
  ,@RoleName
PRINT @RC
GO



-- now, we will see if GetRolesByUser works correctly again
-- we add the IT DEPARTMENT group to the FMSADMINISTRATOR group 
-- in result, WGINT\klehs208 should be in the SecurityAdministrator and StandardUser role

-- should return 0
DECLARE @RC int
DECLARE @GroupName nvarchar(256) = N'FMSAdministrators'
DECLARE @AssociatedGroupName nvarchar(256) = N'IT Department'
DECLARE @InsertingUserName nvarchar(256) = N'WGINT\nichsr27'
EXECUTE @RC = [EnterpriseServices].[Core].[p_GroupAssociations_insertAssociatedGroups] 
   @GroupName
  ,@AssociatedGroupName
  ,@InsertingUserName
PRINT @RC
GO
-- let us see the data. WGINT\klehs208 should be in SecurityAdministrator and StandardUser now
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\klehs208'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_GetRolesByUser] 
   @ApplicationName
  ,@UserName
select distinct r.* from #RolesResults rr INNER JOIN Core.Roles r ON rr.RoleId = r.RoleId
GO
-- should return 1
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\klehs208'
DECLARE @RoleName nvarchar(256) = N'SecurityAdministrator'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_IsUserInRole] 
   @ApplicationName
  ,@UserName
  ,@RoleName
PRINT @RC
GO
-- let us see the data. WGINT\lagub447 should be in SecurityAdministrator and StandardUser now
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\lagub447'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_GetRolesByUser] 
   @ApplicationName
  ,@UserName
select distinct r.* from #RolesResults rr INNER JOIN Core.Roles r ON rr.RoleId = r.RoleId
GO
-- should return 1
DECLARE @RC int
DECLARE @ApplicationName nvarchar(256) = N'FMS'
DECLARE @UserName nvarchar(256) = N'WGINT\lagub447'
DECLARE @RoleName nvarchar(256) = N'SecurityAdministrator'
EXECUTE @RC = [EnterpriseServices].[Core].[p_Roles_IsUserInRole] 
   @ApplicationName
  ,@UserName
  ,@RoleName
PRINT @RC
GO







-- check the delete trigger on GroupUsers tr_GroupUsers_dCheckOwnership
-- should raise an error
DECLARE @GroupId uniqueidentifier, @UserId uniqueidentifier
EXEC Core.p_Groups_GetGroupByName N'FMS', @GroupId OUTPUT
EXEC Core.p_Users_GetUserByName N'WGINT\nichsr27', @UserId OUTPUT
DELETE FROM Core.GroupUsers WHERE @GroupId = GroupId AND @UserId = UserId
GO
-- nothing should happen, because this record does not exist
DECLARE @GroupId uniqueidentifier, @UserId uniqueidentifier
EXEC Core.p_Groups_GetGroupByName N'FMS', @GroupId OUTPUT
EXEC Core.p_Users_GetUserByName N'WGINT\lagub447', @UserId OUTPUT
DELETE FROM Core.GroupUsers WHERE @GroupId = GroupId AND @UserId = UserId
GO
-- add WGINT\lagub447 to the FMS group as a member
DECLARE @RC int
EXEC @RC = Core.p_GroupUsers_insertGroupUser N'FMS', N'WGINT\lagub447', 0, 0, 1, N'WGINT\klehs208'
PRINT @RC
GO
-- should raise an error
DECLARE @GroupId uniqueidentifier, @UserId uniqueidentifier
EXEC Core.p_Groups_GetGroupByName N'FMS', @GroupId OUTPUT
EXEC Core.p_Users_GetUserByName N'WGINT\nichsr27', @UserId OUTPUT
DELETE FROM Core.GroupUsers WHERE @GroupId = GroupId AND @UserId = UserId
GO
-- should delete exactly one row
DECLARE @GroupId uniqueidentifier, @UserId uniqueidentifier
EXEC Core.p_Groups_GetGroupByName N'FMS', @GroupId OUTPUT
EXEC Core.p_Users_GetUserByName N'WGINT\lagub447', @UserId OUTPUT
DELETE FROM Core.GroupUsers WHERE @GroupId = GroupId AND @UserId = UserId
GO

-- add WGINT\lagub447 to the FMS group as an Owner
--	should return 5
DECLARE @RC int
EXEC @RC = Core.p_GroupUsers_insertGroupUser N'FMS', N'WGINT\lagub447', 1, 0, 1, N'WGINT\klehs208'
PRINT @RC
GO

--	should return 0
DECLARE @RC int
EXEC @RC = Core.p_GroupUsers_insertGroupUser N'FMS', N'WGINT\lagub447', 1, 0, 1, N'WGINT\nichsr27'
PRINT @RC
GO

-- should raise an error and the transaction should be rolled back
DECLARE @GroupId uniqueidentifier, @UserIdScott uniqueidentifier, @UserIdBrian uniqueidentifier
EXEC Core.p_Groups_GetGroupByName N'FMS', @GroupId OUTPUT
EXEC Core.p_Users_GetUserByName N'WGINT\nichsr27', @UserIdScott OUTPUT
EXEC Core.p_Users_GetUserByName N'WGINT\lagub447', @UserIdBrian OUTPUT
DELETE FROM Core.GroupUsers WHERE (@GroupId = GroupId AND @UserIdScott = UserId) OR
									(@GroupId = GroupId AND @UserIdBrian = UserId)
GO
-- should return WGINT\lagub447 and WGINT\nichsr27 as the group owners of the FMS group
SELECT u.UserName, g.GroupName, gu.* FROM Core.Users u INNER JOIN Core.GroupUsers gu ON u.UserId = gu.UserId
							INNER JOIN Core.Groups g ON gu.GroupId = g.GroupId
	WHERE (u.LoweredUserName = N'WGINT\nichsr27' OR u.LoweredUserName = N'WGINT\lagub447') AND g.LoweredGroupName = N'fms'
-- should delete exactly one row
DECLARE @GroupId uniqueidentifier, @UserId uniqueidentifier
EXEC Core.p_Groups_GetGroupByName N'FMS', @GroupId OUTPUT
EXEC Core.p_Users_GetUserByName N'WGINT\lagub447', @UserId OUTPUT
DELETE FROM Core.GroupUsers WHERE (@GroupId = GroupId AND @UserId = UserId)
GO
-- should return WGINT\nichsr27 as the group owners of the FMS group
SELECT u.UserName, g.GroupName, gu.* FROM Core.Users u INNER JOIN Core.GroupUsers gu ON u.UserId = gu.UserId
							INNER JOIN Core.Groups g ON gu.GroupId = g.GroupId
	WHERE (u.LoweredUserName = N'WGINT\nichsr27' OR u.LoweredUserName = N'WGINT\lagub447') AND g.LoweredGroupName = N'fms'







